package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.DangKyModel;
import model.SinhVienModel;
import model.MonHocModel;
import util.DataUtil;
import common.Constant;

public class DangKyDAO {
	//thong ke	
	public static DangKyModel ChiTietThongKe(String maSinhVien) {
		DangKyModel dangKyModel=new DangKyModel();
		try {
			CallableStatement csmt = DataUtil
					.getConnection()
					.prepareCall("{call sp_ThongKeMonHocByMaSV(?)}");
			csmt.setString(1, maSinhVien);			
			ResultSet rs = csmt.executeQuery();			
			while (rs.next()) {				
				dangKyModel.setTongChungChi(rs.getString("TongChungChi"));
				dangKyModel.setTongLePhi(rs.getString("TongLePhi"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dangKyModel;
	}
	
	// hien thi danh sach mon hoc da dang ky
	
	public static ArrayList<SinhVienModel> getSinhVienChuaDangKy(String MaMH) {
		ArrayList<SinhVienModel> sinhVienModelList = new ArrayList<SinhVienModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllSinhVienChuaDangKy"));
			preparedStatement.setString(1, MaMH);			
			ResultSet rs = preparedStatement.executeQuery();
			SinhVienModel sinhVienModel;
			while (rs.next()) {
				sinhVienModel=new SinhVienModel();
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));
				sinhVienModelList.add(sinhVienModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sinhVienModelList;
	}
	
	public static ArrayList<SinhVienModel> getSinhVienDaDangKy(String MaMH) {
		ArrayList<SinhVienModel> sinhVienModelList = new ArrayList<SinhVienModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllSinhVienDaDangKy"));
			preparedStatement.setString(1, MaMH);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				SinhVienModel sinhVienModel=new SinhVienModel();
				sinhVienModel.setMaSV(rs.getString("MaSinhVien"));
				sinhVienModel.setTenSV(rs.getString("TenSinhVien"));
				sinhVienModelList.add(sinhVienModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sinhVienModelList;
	}
	
	//insert mon hoc cho sinh vien
	public static Boolean dangKyMonHoc(DangKyModel dangKyModel){
		try {
			CallableStatement csmt = DataUtil
				.getConnection()
				.prepareCall("{call sp_DangKyMonHoc(?,?)}");
			csmt.setNString("MaSV",dangKyModel.getMaSV());
			csmt.setNString("MaMH",dangKyModel.getMaMH());
	
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	//xoa mon hoc
	public static boolean insertSinhVienDangKy(DangKyModel dangKy)
	{		
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
			.getConnection()
			.prepareCall("{call sp_InsertSinhVienDangKy(?,?)}");
			csmt.setNString(1,dangKy.getMaSV());
			csmt.setNString(2,dangKy.getMaMH());			
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}				
		return result;
	}
	
	public static boolean deleteSinhVienDangKy(String MaMH,String MaSV)
	{
		
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
			.getConnection()
			.prepareCall("{call sp_DeleteSinhVienDangKy(?,?)}");
			csmt.setNString(1,MaSV);
			csmt.setNString(2,MaMH);			
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}				
		return result;
	}
	
	// danh sach mon hoc sinh vien dang ky
	
	public static ArrayList<MonHocModel> getMonHocChuaDangKy(String MaSV) {
		ArrayList<MonHocModel> monHocModelList = new ArrayList<MonHocModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllMonHocChuaDangKy"));
			preparedStatement.setString(1, MaSV);			
			ResultSet rs = preparedStatement.executeQuery();
			MonHocModel monHocModel;
			while (rs.next()) {
				monHocModel=new MonHocModel();
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				monHocModelList.add(monHocModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return monHocModelList;
	}
	
	public static ArrayList<MonHocModel> getMonHocDaDangKy(String MaSV) {
		ArrayList<MonHocModel> monHocModelList = new ArrayList<MonHocModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllMonHocDaDangKy"));
			preparedStatement.setString(1, MaSV);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				MonHocModel monHocModel=new MonHocModel();
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				monHocModelList.add(monHocModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return monHocModelList;
	}
	
	//xoa sinh vien
	
	public static boolean insertMonHocDangKy(DangKyModel dangKy)
	{		
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
			.getConnection()
			.prepareCall("{call sp_InsertMonHocDangKy(?,?)}");
			csmt.setNString(1,dangKy.getMaSV());
			csmt.setNString(2,dangKy.getMaMH());			
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}				
		return result;
	}
	
	public static boolean deleteMonHocDangKy(String MaMH,String MaSV)
	{		
		Boolean result = false;		
		try {
			CallableStatement csmt = DataUtil
			.getConnection()
			.prepareCall("{call sp_DeleteMonHocDangKy(?,?)}");
			csmt.setNString(1,MaSV);
			csmt.setNString(2,MaMH);
			return DataUtil.executeNonStore(csmt);
		} catch (Exception e) {
			e.printStackTrace();
			result = false;
		}				
		return result;
	}
	
	// xem mon hoc -- danh sach tat ca mon hoc
	// hien thi danh sach mon hoc da dang ky
	public static ArrayList<DangKyModel> showAllMonHocByMaSV(String maSinhVien) {
		ArrayList<DangKyModel> dangKyModelList = new ArrayList<DangKyModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllMonHocDangKy"));
			preparedStatement.setString(1, maSinhVien);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				DangKyModel dangKyModel=new DangKyModel();
				dangKyModel.setMaMH(rs.getString("MaMonHoc"));
				dangKyModel.setTenMH(rs.getString("TenMonHoc"));
				dangKyModel.setThoiGianBatDau(rs.getString("ThoiGianBatDau"));
				dangKyModel.setThoiGianKetThuc(rs.getString("ThoiGianKetThuc"));
				dangKyModel.setChungChi(rs.getString("ChungChi"));
				dangKyModel.setLePhi(rs.getString("LePhi"));
				
				dangKyModelList.add(dangKyModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dangKyModelList;
	}
	
	public static ArrayList<DangKyModel> showAllSinhVienByMaMH(String maMonHoc) {
		ArrayList<DangKyModel> dangKyModelList = new ArrayList<DangKyModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllSinhVienDangKy"));
			preparedStatement.setString(1, maMonHoc);			
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				DangKyModel dangKyModel=new DangKyModel();
				dangKyModel.setMaSV(rs.getString("MaSinhVien"));
				dangKyModel.setTenSV(rs.getString("TenSinhVien"));				
				dangKyModel.setNgaySinh(rs.getString("NgaySinh"));
				dangKyModel.setGioiTinh(rs.getString("GioiTinh"));
				dangKyModel.setDiaChi(rs.getString("DiaChi"));
				
				dangKyModelList.add(dangKyModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dangKyModelList;
	}
	
	//dang nhap quyen sinh vien --- hien thi danh sach mon hoc chua dang ky	
	public static ArrayList<MonHocModel> showAllMonhocChuaDangKyByMaSV(String MaSV) {
		ArrayList<MonHocModel> monHocModelList = new ArrayList<MonHocModel>();		
		try {
			PreparedStatement preparedStatement = DataUtil
					.getConnection()
					.prepareStatement(
							Constant.SQL_RES
									.getString("dangky.sql.showAllMonhocChuaDangKyByMaSV"));
			preparedStatement.setString(1, MaSV);			
			ResultSet rs = preparedStatement.executeQuery();
			MonHocModel monHocModel;
			while (rs.next()) {
				monHocModel=new MonHocModel();
				monHocModel.setMaMH(rs.getString("MaMonHoc"));
				monHocModel.setTenMH(rs.getString("TenMonHoc"));
				monHocModel.setThoiGianBatDau(rs.getString("ThoiGianBatDau"));
				monHocModel.setThoiGianKetThuc(rs.getString("ThoiGianKetThuc"));
				monHocModel.setChungChi(rs.getString("ChungChi"));
				monHocModel.setLePhi(rs.getString("LePhi"));
				monHocModelList.add(monHocModel);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return monHocModelList;
	}
}
